<?xml version="1.0"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xsl:output method="text" encoding="UTF-8" />

<xsl:template name="replace-string">
  <xsl:param name="text"/>
  <xsl:param name="from"/>
  <xsl:param name="to"/>

  <xsl:choose>
    <xsl:when test="contains($text, $from)">

      <xsl:variable name="before" select="substring-before($text, $from)"/>
      <xsl:variable name="after" select="substring-after($text, $from)"/>
      <xsl:variable name="prefix" select="concat($before, $to)"/>

      <xsl:value-of select="$before"/>
      <xsl:value-of select="$to"/>
      <xsl:call-template name="replace-string">
        <xsl:with-param name="text" select="$after"/>
        <xsl:with-param name="from" select="$from"/>
        <xsl:with-param name="to" select="$to"/>
      </xsl:call-template>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="$text"/>
    </xsl:otherwise>
  </xsl:choose>

</xsl:template>

<xsl:template match="/">
      <!-- MEFS -->
      <xsl:for-each select="//MEFS/MEF">
INSERT INTO uni_mef (baseid, libelle_court, libelle_long, id_siecle)
VALUES(1, '<xsl:value-of select="FORMATION" />', '<xsl:value-of select="LIBELLE_LONG" />', '<xsl:value-of select="@CODE_MEF" />');$$
    <br/>
      </xsl:for-each>
      
      <!-- MATIERES -->
      <xsl:for-each select="//MATIERES/MATIERE">
INSERT INTO uni_matiere(baseid, libelle_court, libelle_long, libelle, id_siecle)
VALUES(1,
       '<xsl:value-of select="CODE_GESTION" />',
       '<xsl:value-of select="normalize-space(LIBELLE_LONG)" />',
       '<xsl:value-of select="normalize-space(LIBELLE_EDITION)" />',
       '<xsl:value-of select="@CODE_MATIERE" />'            
);$$
      </xsl:for-each>
      
      <!-- MODALITES ELECTION -->
      <!--
      <xsl:for-each select="//MODALITES_ELECTION/MODALITE_ELECTION">
INSERT INTO s_mod_ele(s_uaj,s_ann,s_me_cod,s_me_lib_cou,s_me_lib_lon)
VALUES('<xsl:value-of select="../../../PARAMETRES/UAJ" />',<xsl:value-of select="../../../PARAMETRES/ANNEE_SCOLAIRE" />,
       '<xsl:value-of select="@CODE_MODALITE_ELECT" />',
       '<xsl:value-of select="LIBELLE_COURT" />',
       '<xsl:value-of select="LIBELLE_LONG" />'
);
      </xsl:for-each>
      -->
      
      <!-- PROGRAMMES -->
      <xsl:for-each select="//PROGRAMMES/PROGRAMME">
INSERT INTO uni_programme(baseid, matiereid, mefid, modalite)
VALUES(1,       
       getMatiereIdByIdSiecle(1, '<xsl:value-of select="CODE_MATIERE" />'),
       getMefByCodeSiecle(1, '<xsl:value-of select="CODE_MEF" />'),
       '<xsl:value-of select="CODE_MODALITE_ELECT" />'
);$$
      </xsl:for-each>
      
      <!-- OPTIONS OBLIGATOIRES -->
      <!--
      <xsl:for-each select="//OPTIONS_OBLIGATOIRES/OPTION_OBLIGATOIRE">
INSERT INTO s_opt_obl(s_uaj,s_ann,s_mf_cod,s_mt_cod,s_oo_ran)
VALUES('<xsl:value-of select="../../../PARAMETRES/UAJ" />',<xsl:value-of select="../../../PARAMETRES/ANNEE_SCOLAIRE" />,
       '<xsl:value-of select="CODE_MEF" />',
       '<xsl:value-of select="CODE_MATIERE" />',
       <xsl:value-of select="RANG_OPTION" />
);
      </xsl:for-each>
      -->
 </xsl:template>

</xsl:stylesheet>